﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class PhieuGuiHangDAO
    {
        public List<PhieuGuiHangDTO> HienThi()
        {
            string sql = "Select PGH.MAPGH, PGH.NGAYGUI, SUM(SOLUONG) From PHIEUGUIHANG PGH, CTPHIEUGUIHANG CTPGH where PGH.MAPGH = CTPGH.MAPGH GROUP BY PGH.MAPGH, PGH.NGAYGUI";
            SqlConnection conn = DataProvider.ConnectionDB();
            SqlCommand command = new SqlCommand(sql, conn);
            SqlDataReader reader = command.ExecuteReader();

            List<PhieuGuiHangDTO> danhsach = new List<PhieuGuiHangDTO>();
            while (reader.Read())
            {
                PhieuGuiHangDTO dto = new PhieuGuiHangDTO();
                dto.MAPGH = reader.GetString(0);
                dto.NGAYGUI = reader.GetDateTime(1);
                dto.SOLUONG = reader.GetInt32(2);
                danhsach.Add(dto);
            }
            conn.Close();
            return danhsach;
        }

        public int ThemPGH(PhieuGuiHangDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "insert into PHIEUGUIHANG values(";
            sql += "N'" + dto.MAPGH + "',";
            sql += "'" + dto.NGAYGUI + "')";                       

            SqlCommand command = new SqlCommand(sql, conn);
            int kq = command.ExecuteNonQuery();
            conn.Close();
            if (kq <= 0)
                return 0;
            return 1;
        }

        public int CapNhatPGH(PhieuGuiHangDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "update PHIEUGUIHANG ";
            sql += "set MAPGH=";
            sql += "'" + dto.MAPGH + "',";
            sql += "NGAYGUI=";
            sql += "'" + dto.NGAYGUI + "'";
            sql += " where MAPGH=";
            sql += "'" + dto.MAPGH + "'";

            SqlCommand command = new SqlCommand(sql, conn);
            int kq = command.ExecuteNonQuery();
            conn.Close();
            if (kq <= 0)
                return 0;
            return 1;
        }

        public int KiemTraMAPGH(string mapg)
        {
            SqlConnection conn = DataProvider.ConnectionDB();
            string sql = "Select count(*) from PHIEUGUIHANG where MAPGH=";
            sql += "'" + mapg + "'";
            SqlCommand command = new SqlCommand(sql, conn);
            int kq = (int)command.ExecuteScalar();
            conn.Close();
            if (kq <= 0)
                return 0;
            return 1;
        }
    }
}
